<?php

/* Tous les posts ------------------------------------------ */

function post_get_list($type)
{
	$sql = 'SELECT p.id, p.title, p.content, p.time_submit, p.idAuthor,
	        	m.name AS author_name, m.firstname AS author_firstname
	        FROM Post p
	        LEFT JOIN Member m ON m.id = p.idAuthor
	        WHERE p.type = "'.$type.'"
	        ORDER BY p.time_submit DESC;';
	$data = sql_query($sql);
	return $data;
}


/**
 * Obtenir tous les posts d'une page
 * @param page_num: numéro de la page
 * @return record set
 */
function post_get_page($page_num, $type)
{
	$first = ($page_num - 1) * NEWS_PER_PAGE;
	$sql = 'SELECT p.id, p.title, p.content, p.time_submit, p.idAuthor,
				m.name AS author_name, m.firstname AS author_firstname
	        FROM Post p
	        LEFT JOIN Member m ON m.id = p.idAuthor
			WHERE p.type = "'.$type.'"
	        ORDER BY p.time_submit DESC
	        LIMIT '.$first.', '.NEWS_PER_PAGE.';';
	$data = sql_query($sql);
	return $data;
}


function post_get_by_id($id)
{
	$id = intval($id);
	$sql = 'SELECT p.title, p.content, p.time_submit, p.idAuthor,
	        	m.name AS author_name, m.firstname AS author_firstname,
	        	s.label as author_status_label, s.color as author_status_color
	        FROM Post p
	        LEFT JOIN Member m ON p.idAuthor = m.id
	        LEFT JOIN MemberStatus s on s.id = m.status_id
	        WHERE p.id = '.$id.';';
	$data = sql_query($sql);
	return mysql_fetch_assoc($data);
}


/**
 * Obtenir le nombre de post
 */
function post_count($type)
{
	$sql = 'SELECT COUNT(p.id) AS total
	        FROM Post p
	        WHERE p.type = "'.$type.'";';
	$data = sql_query($sql);
	$row = mysql_fetch_assoc($data);
	return $row['total'];
}


function post_add($title, $content, $author, $type)
{
	$title = mysql_real_escape_string($title);
	$content = mysql_real_escape_string($content);
	$author = intval($author);
	$type = mysql_real_escape_string($type);
	$sql = 'INSERT INTO Post (title, content, time_submit, type, idAuthor)
	        VALUES ("'.$title.'", "'.$content.'", '.time().', "'.$type.'", '.$author.');';
	sql_query($sql);
}


function post_update($id, $title, $content)
{
	$id = intval($id);
	$title = mysql_real_escape_string($title);
	$content = mysql_real_escape_string($content);
	$sql = 'UPDATE Post
	        SET title = "'.$title.'", content = "'.$content.'"
	        WHERE id = '.$id.';';
	sql_query($sql);
}


/**
 * Supprimer un post et toutes ses réponses
 * @param id: id du post
 */
function post_delete($id)
{
	$id = intval($id);
	$sql = 'DELETE FROM Answer
	        WHERE idPost = '.$id.';';
	sql_query($sql);
	$sql = 'DELETE FROM Post
	        WHERE id = '.$id.';';
	sql_query($sql);
}


function post_grant_edit($id)
{
	$id = intval($id);
	// admin, modo, ou auteur
	if ($_SESSION['status_code'] == 'ADMIN'
		|| $_SESSION['status_code'] == 'MODERATOR'
		|| $_SESSION['id'] == $id)
	{
		return true;
	}
	return false;
}

function post_add_view_count($id)
{
	$id = intval($id);
	$sql = 'UPDATE Post
	        SET view_count = (view_count + 1)
	        WHERE id = '.$id.';';
	sql_query($sql);
}

/* News uniquement ----------------------------------------- */

function news_get_list()
{
	$data = post_get_list('N');
	return $data;
}


function news_get_page($page_num)
{
	$data = post_get_page($page_num, 'N');
	return $data;
}


function news_count()
{
	$count = post_count('N');
	return $count;
}


function news_add($title, $content, $author)
{
	post_add($title, $content, $author, 'N');
	news_feed_rss();
}

/**
 * Générer le flux RSS des dernières news de la 1ère page
 */
function news_feed_rss()
{
	$rss = fopen(FLUX_RSS, 'w');
	if ($rss)
	{
		fputs($rss, '<?xml version="1.0" encoding="utf-8" ?>'."\n");
		fputs($rss, '<rss version="2.0">'."\n");
		fputs($rss, "\t<channel>\n");
		fputs($rss, "\t\t<title>Adrenaline-Escalade.com</title>\n");
		fputs($rss, "\t\t<link>".WEBLINK."</link>\n");
		fputs($rss, "\t\t<description>Les dernières nouvelles du club</description>\n");
		fputs($rss, "\t\t<lastBuildDate>".date('r', time())."</lastBuildDate>\n");
		fputs($rss, "\t\t<language>fr</language>\n");

		$image = "
		<image>
			<title>Adrenaline-Escalade.com - Actualités</title>
			<url>".WEBLINK."design/images/adrenaline.png</url>
			<link>".WEBLINK."</link>
			<width>322</width>
			<height>47</height>
		</image>\n";

		fputs($rss, $image);

		$rec = news_get_page(1);
		while ($row = mysql_fetch_assoc($rec))
		{
			fputs($rss, "\t\t<item>\n");
			fputs($rss, "\t\t\t<title>".$row['title']."</title>\n");
			fputs($rss, "\t\t\t<link>".WEBLINK."club-actualites.html#news-".$row['id']."</link>\n");
			fputs($rss, "\t\t\t<description>".html_entity_decode(strip_tags($row['content']), ENT_QUOTES, 'UTF-8')."</description>\n");
			fputs($rss, "\t\t\t<pubDate>".date('r', $row['time_submit'])."</pubDate>\n");
			fputs($rss, "\t\t</item>\n");
		}
		fputs($rss, "\t</channel>\n");
		fputs($rss, "</rss>\n");
		fclose($rss);
	}
}

// Forum -----------------------------------------------------------------------

/**
 * Obtenir liste des annonces
 * @param viewer: id du membre qui demande la liste les sujets
 */
function ann_get_list($page_num = NULL, $viewer = 0)
{
	// mutha fucka query

	/*
	answer_last_submit = timestamp dernière réponse, ou timestamp post si 0 réponse
	answer_count = nombre de réponses à ce post
	answer_author = auteur dernière réponse
	viewer_count = nombre de réponses de $viewer

	trié par dernière réponse, ou heure du post si 0 réponse
	*/
	$viewer = intval($viewer);
	$sql = 'SELECT p.id,
                   p.title,
                   p.content,
                   p.time_submit,
                   p.view_count,
                   p.idAuthor AS author_id,
                   m.name AS author_name,
                   m.firstname AS author_firstname,
                   s.color AS author_status_color,

                   IFNULL(a.time_submit, p.time_submit) AS answer_last_submit,
                   (
                    SELECT COUNT(a2.id)
                    FROM Answer a2
                    WHERE a2.idPost = p.id
                   ) AS answer_count,

                   CONCAT(m3.firstname, " ", m3.name) AS answer_author,
                   answer_status.color AS answer_status_color,

                   (
                    SELECT COUNT(a4.idAuthor)
                    FROM Answer a4
                    WHERE a4.idPost = p.id AND a4.idAuthor = '.$viewer.'
                   ) AS viewer_count
            FROM Post p
            LEFT JOIN Member m ON m.id = p.idAuthor
            LEFT JOIN Answer a ON a.idPost = p.id
            LEFT JOIN MemberStatus s ON m.status_id = s.id
            LEFT JOIN Member m3 ON m3.id = a.idAuthor
            LEFT JOIN MemberStatus answer_status ON answer_status.id = m3.status_id
            WHERE p.type = "A"
                  AND (a.time_submit = (SELECT MAX(a3.time_submit)
                                        FROM Answer a3
                                        WHERE a3.idPost = p.id)
                       OR a.time_submit IS NULL)
            GROUP BY p.id,
                     p.title,
                     p.content,
                     p.time_submit,
                     p.idAuthor,
                     author_name,
                     author_firstname,
                     answer_last_submit
            ORDER BY answer_last_submit DESC';
	if ($page_num != NULL) {
		$first = ($page_num - 1) * TOPIC_PER_PAGE;
		$sql .= ' LIMIT '.$first.', '.TOPIC_PER_PAGE.';';
	}

	$data = sql_query($sql);
	return $data;
}

function ann_count()
{
	return post_count('A');
}

/**
 * Ajouter une annonce
 */
function ann_add($title, $message, $author_id)
{
	post_add($title, $message, $author_id, 'A');
	forum_feed_rss();
}


function ann_count_answer($id_post)
{
	$id_post = intval($id_post);
	$sql = 'SELECT COUNT(id)
	        FROM Answer
	        WHERE idPost = '.$id_post.';';
	$data = sql_query($sql);
	$row = mysql_fetch_row($data);
	return $row[0];
}


/**
 * Obtenir les réponses d'une annonce
 * @param id: id de l'annonce
 */
function ann_get_answers($id, $page_num = 1)
{
	$id = intval($id);
	$first = ($page_num - 1) * POST_PER_PAGE;
	$sql = 'SELECT a.id, a.content, a.time_submit,
                   a.idAuthor AS author_id,
	               m.name AS author_name,
	               m.firstname AS author_firstname,
	               s.label as author_status_label, s.color as author_status_color
            FROM Answer a
            LEFT JOIN Member m ON m.id = a.idAuthor
            LEFT JOIN MemberStatus s on s.id = m.status_id
            WHERE a.idPost = '.$id.'
            ORDER BY a.time_submit
            LIMIT '.$first.', '.POST_PER_PAGE.';';
	$data = sql_query($sql);
	return $data;
}

/**
 * Obtenir une réponse
 * @param id: id de la réponse
 * @return array ou NULL
 */
function ann_get_answer_by_id($id)
{
	$id = intval($id);
	$sql = 'SELECT a.id, a.content, a.time_submit,
   	               a.idAuthor AS author_id,
                   m.name AS author_name,
                   m.firstname AS author_firstname,
                   a.idPost AS post_id
            FROM Answer a
            LEFT JOIN Member m ON m.id = a .idAuthor
            WHERE a.id = '.$id.';';
	$data = sql_query($sql);
	return mysql_fetch_assoc($data);
}


/**
 * Ajouter une réponse à une annonce
 */
function ann_add_answer($id_post, $id_author, $message)
{
	$id_post = intval($id_post);
	$id_author = intval($id_author);
	$message = mysql_real_escape_string($message);
	$sql = 'INSERT INTO Answer (content, idAuthor, idPost, time_submit)
	        VALUES ("'.$message.'", '.$id_author.', '.$id_post.', '.time().');';
	sql_query($sql);
	forum_feed_rss();
}

function ann_update_answer($id, $content)
{
	$id = intval($id);
	$content = mysql_real_escape_string($content);
	$sql = 'UPDATE Answer
	        SET content = "'.$content.'"
	        WHERE id = '.$id.';';
	sql_query($sql);
	forum_feed_rss();
}


function ann_delete_answer($id)
{
	$id = intval($id);
	$sql = 'DELETE FROM Answer
	        WHERE id = '.$id.';';
	sql_query($sql);
	forum_feed_rss();
}


function forum_feed_rss()
{
	$rss = fopen(FLUX_RSS_FORUM, 'w');
	if ($rss)
	{
		fputs($rss, '<?xml version="1.0" encoding="utf-8" ?>'."\n");
		fputs($rss, '<rss version="2.0">'."\n");
		fputs($rss, "\t<channel>\n");
		fputs($rss, "\t\t<title>adrenaline-ascalade.com - Forum</title>\n");
		fputs($rss, "\t\t<link>".WEBLINK."annonces.html</link>\n");
		fputs($rss, "\t\t<description>Derniers messages du forum</description>\n");
		fputs($rss, "\t\t<lastBuildDate>".date('r', time())."</lastBuildDate>\n");
		fputs($rss, "\t\t<language>fr</language>\n");

		$rec = ann_get_list(1);

		while ($row = mysql_fetch_assoc($rec))
		{
			fputs($rss, "
			<item>
				<title>".$row['title']."</title>
				<link>".WEBLINK."annonces-voir-".$row['id'].".html</link>
				<description>".$row['answer_count']. " réponse(s), dernière activité le ".time_to_str($row['answer_last_submit'])." par ".($row['answer_author'] == '' ? ($row['author_firstname'].' '.$row['author_name']) : $row['answer_author'])."</description>
				<pubDate>".date('r', $row['time_submit'])."</pubDate>
			</item>\n");
		}
		fputs($rss, "\t</channel>\n");
		fputs($rss, "</rss>\n");
		fclose($rss);
	}
}
